
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
from matplotlib import ticker
import matplotlib.patches as mpatches
import geopandas as gpd
from matplotlib.colors import LinearSegmentedColormap
import shapely as sh
from shapely.geometry import mapping
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import plotly.express as px
from plotly.offline import init_notebook_mode
init_notebook_mode(connected=False)
import warnings
warnings.filterwarnings("ignore")


# Reading and melting the first data
df = pd.read_excel("data/ages_parties.xlsx")
df_first_2013 = df.loc[(df["year"]==2013) & (df["votes"]=="first")]
df_first_2013 = pd.melt(df_first_2013, id_vars=['age_group', "year", "votes"]).sort_values(['age_group'])


# Melting the second data
df_second_2013 = df.loc[(df["year"]==2013) & (df["votes"]=="second")]
df_second_2013 = pd.melt(df_second_2013, id_vars=['age_group', "year", "votes"]).sort_values(['age_group'])


# Defining palette for assign each party a color
palette_bright = sns.palettes.SEABORN_PALETTES["bright"]
palette_dark = sns.palettes.SEABORN_PALETTES["dark"]
color_to_cat = dict(CDU = palette_dark[0],
                    CSU = palette_dark[-1],
                    SPD = palette_bright[3],
                    GRÜNE = palette_bright[2],
                    OTHER = palette_bright[7],
                    DIE_LINKE = palette_bright[4],
                    FDP = palette_bright[-2],
                    AfD = palette_bright[-1])


sns.set_style("white")
# Customizing the first figure
fig, ax = plt.subplots(1,2, figsize=(15, 9))
sns.barplot(ax=ax[0],
            y='age_group',
            x='value',
            hue='variable',
            data=df_first_2013,
            palette=color_to_cat,
            ci=None)

plt.title("2013 German Federal Election ", x=-0.1, y=1.08, fontsize = 23)
ax[0].set_title("First Votes by Age Groups", x=0.23, y=1, fontsize=15)
ax[0].set_xlabel("Number of Votes", fontsize = 16)
ax[0].set_ylabel("")
ax[0].set(yticks=[])
ax[0].tick_params(labelsize=15)
ax[0].xaxis.set_major_formatter(ticker.EngFormatter())
ax[0].legend([])
fig.axes[0].invert_xaxis()


# Customizing the second figure
sns.barplot(ax=ax[1],
            y='age_group',
            x='value',
            hue='variable',
            data=df_second_2013,
            palette=color_to_cat,
            ci=None)

plt.suptitle("Second Votes by Age Groups", x=0.632, y=0.905, fontsize=14)
ax[1].set_xlabel("Number of Votes", fontsize = 16)
ax[1].set_ylabel("", fontsize = 16)
ax[1].tick_params(labelsize=15)
ax[1].tick_params(axis="y", pad=15)
ax[1].xaxis.set_major_formatter(ticker.EngFormatter())
ax[1].legend(title="", fontsize=10)

<matplotlib.legend.Legend at 0x1665e491280>


# Melting the first data
df_first_2017 = df.loc[(df["year"]==2017) & (df["votes"]=="first")]
df_first_2017 = pd.melt(df_first_2017, id_vars=['age_group', "year", "votes"]).sort_values(['age_group'])


# Melting the second data
df_second_2017 = df.loc[(df["year"]==2017) & (df["votes"]=="second")]
df_second_2017 = pd.melt(df_second_2017, id_vars=['age_group', "year", "votes"]).sort_values(['age_group'])


sns.set_style("white")
# Customizing the first figure
fig, ax = plt.subplots(1,2, figsize=(15, 9))
sns.barplot(ax=ax[0], y='age_group',
            x='value',
            hue='variable',
            data=df_first_2017,
            palette=color_to_cat,
            ci=None)

plt.title("2017 German Federal Election ", x=-0.1, y=1.08, fontsize = 23)
ax[0].set_title("First Votes by Age Groups", x=0.23, y=1, fontsize=15)
ax[0].set_xlabel("Number of Votes", fontsize = 16)
ax[0].set_ylabel("")
ax[0].set(yticks=[])
ax[0].tick_params(labelsize=15)
ax[0].xaxis.set_major_formatter(ticker.EngFormatter())
ax[0].legend([])
fig.axes[0].invert_xaxis()


# Customizing the second figure
sns.barplot(ax=ax[1],
            y='age_group',
            x='value',
            hue='variable',
            data=df_second_2017,
            palette=color_to_cat,
            ci=None)

plt.suptitle("Second Votes by Age Groups", x=0.632, y=0.905, fontsize=14)
ax[1].set_xlabel("Number of Votes", fontsize = 16)
ax[1].set_ylabel("", fontsize = 16)
ax[1].tick_params(labelsize=15)
ax[1].tick_params(axis="y", pad=15)
ax[1].xaxis.set_major_formatter(ticker.EngFormatter())
ax[1].legend(title="", fontsize=10)

<matplotlib.legend.Legend at 0x1665eda6220>


# Setting the index of the dataframe
df.set_index("age_group", inplace=True)


# Building a dataset of percentage changes between 2017 and 2013 for first votes
df1 = df.loc[(df["year"]==2013) & (df["votes"]=="first")].drop(["votes", "year"], axis=1)
df2 = df.loc[(df["year"]==2017) & (df["votes"]=="first")].drop(["votes", "year"], axis=1)
pct_change = (df2 - df1) / df1 * 100


# Adding votes column
pct_change["votes"] = df["votes"][:6]
pct_change.reset_index(inplace=True)


# Melting the data
pct_change = pd.melt(pct_change, id_vars=['age_group', "votes"]).sort_values(['age_group'])


sns.set_style("white")
# Customizing the figure
fig, ax = plt.subplots(figsize=(15, 9))
sns.barplot(ax=ax,
            x='age_group',
            y='value',
            hue='variable',
            data=pct_change,
            palette=color_to_cat,
            ci=None)

ax.set_title("Percentage Change of First Votes Between 2017 and 2013 by Age Groups", x=0.4, y=1, fontsize=20)
ax.set_xlabel("Age Groups", fontsize = 16)
ax.set_ylabel("% Percentage Change", fontsize=16)
ax.tick_params(labelsize=15)
ax.legend(title="", fontsize=10, bbox_to_anchor=(1.01, 1))

<matplotlib.legend.Legend at 0x1665eeb88b0>


# Building a dataset of percentage changes between 2017 and 2013 for first votes
df1 = df.loc[(df["year"]==2013) & (df["votes"]=="second")].drop(["votes", "year"], axis=1)
df2 = df.loc[(df["year"]==2017) & (df["votes"]=="second")].drop(["votes", "year"], axis=1)
pct_change = (df2 - df1) / df1 * 100


# Adding votes column
pct_change["votes"] = df["votes"][:6]
pct_change.reset_index(inplace=True)


# Melting the data
pct_change = pd.melt(pct_change, id_vars=['age_group', "votes"]).sort_values(['age_group'])


sns.set_style("white")
# Customizing the figure
fig, ax = plt.subplots(figsize=(15, 9))
sns.barplot(ax=ax,
            x='age_group',
            y='value',
            hue='variable',
            data=pct_change,
            palette=color_to_cat,
            ci=None)

ax.set_title("Percentage Change of Second Votes Between 2017 and 2013 by Age Groups", x=0.42, y=1, fontsize=20)
ax.set_xlabel("Age Groups", fontsize = 16)
ax.set_ylabel("% Percentage Change", fontsize=16)
ax.tick_params(labelsize=15)
ax.legend(title="", fontsize=10, bbox_to_anchor=(1.01, 1))

<matplotlib.legend.Legend at 0x1665f1fd550>


# Defining the dataset
df1 = df.loc[(df["year"]==2013) & (df["votes"]=="second")].drop(["year", "votes"], axis=1).sort_values(['age_group'])


# Creating subplots, using 'domain' type for pie charts
specs = [[{'type':'domain'}, {'type':'domain'}, {'type':'domain'}, {'type':'domain'}],
         [{'type':'domain'}, {'type':'domain'}, {'type':'domain'}, {'type':'domain'}]]


# Defining the figure
fig = make_subplots(rows=2, cols=4, specs=specs)


# Defining pie charts
fig.add_trace(go.Pie(labels=df1.index,
                     name="Age Group",
                     values=df1["CDU"],
                     title="CDU",
                     textposition='inside',
                     sort=False), 1, 1)
fig.add_trace(go.Pie(labels=df1.index,
                     name="Age Group",
                     values=df1["SPD"],
                     title="SPD",
                     textposition='inside',
                     sort=False), 1, 2)
fig.add_trace(go.Pie(labels=df1.index,
                     name="Age Group",
                     values=df1["FDP"],
                     title="FDP",
                     textposition='inside',
                     sort=False), 1, 3)
fig.add_trace(go.Pie(labels=df1.index,
                     name="Age Group",
                     values=df1["DIE_LINKE"],
                     title="DIE LINKE",
                     textposition='inside',
                     sort=False), 1, 4)
fig.add_trace(go.Pie(labels=df1.index,
                     name="Age Group",
                     values=df1["GRÜNE"],
                     title="GRÜNE",
                     textposition='inside',
                     sort=False), 2, 1)
fig.add_trace(go.Pie(labels=df1.index,
                     name="Age Group",
                     values=df1["CSU"],
                     title="CSU",
                     textposition='inside',
                     sort=False), 2, 2)
fig.add_trace(go.Pie(labels=df1.index,
                     name="Age Group",
                     values=df1["AfD"],
                     title="AfD",
                     textposition='inside',
                     sort=False), 2, 3)
fig.add_trace(go.Pie(labels=df1.index,
                     name="Age Group",
                     values=df1["OTHER"],
                     title="OTHER",
                     textposition='inside',
                     sort=False), 2, 4)


# Tuning layout and hover info
fig.update_traces(hoverinfo='label+name')
fig.update_layout(width=1000, height=700,
                  legend=dict(orientation="h", x=0.4, y=1.05),
                  title=dict(text='Percentage of Age Groups by Parties ', font_size=20),
                  annotations=[go.layout.Annotation(showarrow=False, text='2013 Second Votes', x=-0.03, y=1.08)])

fig = go.Figure(fig)
fig.show()


# Defining the dataset
df2 = df.loc[(df["year"]==2017) & (df["votes"]=="second")].drop(["year", "votes"], axis=1).sort_values(['age_group'])


# Creating subplots, using 'domain' type for pie charts
specs = [[{'type':'domain'}, {'type':'domain'}, {'type':'domain'}, {'type':'domain'}],
         [{'type':'domain'}, {'type':'domain'}, {'type':'domain'}, {'type':'domain'}]]


# Defining the figure
fig = make_subplots(rows=2, cols=4, specs=specs)


# Defining pie charts
fig.add_trace(go.Pie(labels=df2.index,
                     name="Age Group",
                     values=df2["CDU"],
                     title="CDU",
                     textposition='inside',
                     sort=False), 1, 1)
fig.add_trace(go.Pie(labels=df2.index,
                     name="Age Group",
                     values=df2["SPD"],
                     title="SPD",
                     textposition='inside',
                     sort=False), 1, 2)
fig.add_trace(go.Pie(labels=df2.index,
                     name="Age Group",
                     values=df2["FDP"],
                     title="FDP",
                     textposition='inside',
                     sort=False), 1, 3)
fig.add_trace(go.Pie(labels=df2.index,
                     name="Age Group",
                     values=df2["DIE_LINKE"],
                     title="DIE LINKE",
                     textposition='inside',
                     sort=False), 1, 4)
fig.add_trace(go.Pie(labels=df2.index,
                     name="Age Group",
                     values=df2["GRÜNE"],
                     title="GRÜNE",
                     textposition='inside',
                     sort=False), 2, 1)
fig.add_trace(go.Pie(labels=df2.index,
                     name="Age Group",
                     values=df2["CSU"],
                     title="CSU",
                     textposition='inside',
                     sort=False), 2, 2)
fig.add_trace(go.Pie(labels=df2.index,
                     name="Age Group",
                     values=df2["AfD"],
                     title="AfD",
                     textposition='inside',
                     sort=False), 2, 3)
fig.add_trace(go.Pie(labels=df2.index,
                     name="Age Group",
                     values=df2["OTHER"],
                     title="OTHER",
                     textposition='inside',
                     sort=False), 2, 4)


# Tuning layout and hover info
fig.update_traces(hoverinfo='label+name')
fig.update_layout(width=1000, height=700,
                  legend=dict(orientation="h", x=0.4, y=1.05),
                  title=dict(text='Percentage of Age Groups by Parties ', font_size=20),
                  annotations=[go.layout.Annotation(showarrow=False, text='2017 Second Votes', x=-0.03, y=1.08)])

fig = go.Figure(fig)
fig.show()


# Reading the datasets.(Turnout rates by regions and shapefile of Germany)
shp = gpd.read_file("data/regions_shapefiles/DEU_adm1.shp")
df = pd.read_excel("data/regions_turnout.xlsx")


# Changing column name to merge the datasets
shp.rename(columns={'ID_1':'land_number'}, inplace=True)


# Defining a function to convert shp to geojson format
def df_to_geojson(df, properties, geom='geometry'):
    geojson = {'type':'FeatureCollection', 'features':[]}
    k = 0
    for _, row in df.iterrows():
        features = {'type':'Feature',
                   'properties':{},
                   'geometry':{}}
        features["geometry"] = mapping(row[geom])
        for prop in properties:
            features['properties'][prop] = row[prop]
        geojson['features'].append(features)
        k += 1
    return geojson


# Converting the dataframe to gejson
cols = ["land_number"]
geojson = df_to_geojson(shp, cols)


# Merging two datasets
geo_df = gpd.GeoDataFrame.from_features(
    geojson["features"]
).merge(df, on="land_number").set_index("land_number")


# Plotting the figure
fig = px.choropleth(geo_df,
                    geojson=geo_df["geometry"],
                    locations=geo_df.index,
                    hover_data={"year": False},
                    labels={"turnout": "% Turnout",
                            "year": "Year"},
                    hover_name="Land",
                    facet_col ="year",
                    color="turnout",
                    color_continuous_scale='PuBu',
                    projection="mercator")
fig.update_geos(fitbounds="locations", visible=False)
fig.update_layout(title_text="German Federal Election: Turnout by Regions", width=1000, height=450)

        
fig.show()


# Setting the dataframe
df_2013 = df[df["year"]==2013]


# Setting the figure size
fig, ax = plt.subplots(figsize=(15, 9))


# First plot
sns.barplot(ax=ax,
            x="Entitled_to_vote", 
            y="Land",
            data=df_2013,
            ci=None,
            color='darkgreen')


# Second plot
sns.barplot(ax=ax,
            x="Non_Voters",
            y="Land",
            data=df_2013,
            ci=None,
            color='palegreen')


# Customizing the figure
top_bar = mpatches.Patch(color='darkgreen', label='Voters')
bottom_bar = mpatches.Patch(color='palegreen', label='Non-Voters')
plt.legend(handles=[top_bar, bottom_bar], fontsize=14)
plt.title("Number of Voters and Non-Voters by Regions", y=1.05, x=0.3, fontsize=25)
plt.suptitle("Year: 2013", y=0.915, x=0.155, fontsize=14)
plt.xlabel("", fontsize=16)
plt.ylabel("", fontsize=16)
plt.tick_params(labelsize=15)
ax.xaxis.set_major_formatter(ticker.EngFormatter())

fig.show()


# Setting the dataframe
df_2017 = df[df["year"]==2017]


# Setting the figure size
fig, ax = plt.subplots(figsize=(15, 9))


# First plot
sns.barplot(ax=ax,
            x="Entitled_to_vote", 
            y="Land",
            data=df_2017,
            ci=None,
            color='darkgreen')


# Second plot
sns.barplot(ax=ax,
            x="Non_Voters",
            y="Land",
            data=df_2017,
            ci=None,
            color='palegreen')


# Customizing the figure
top_bar = mpatches.Patch(color='darkgreen', label='Voters')
bottom_bar = mpatches.Patch(color='palegreen', label='Non-Voters')
plt.legend(handles=[top_bar, bottom_bar], fontsize=14)
plt.title("Number of Voters and Non-Voters by Regions", y=1.05, x=0.3, fontsize=25)
plt.suptitle("Year: 2017", y=0.915, x=0.155, fontsize=14)
plt.xlabel("", fontsize=16)
plt.ylabel("", fontsize=16)
plt.tick_params(labelsize=15)
ax.xaxis.set_major_formatter(ticker.EngFormatter())

fig.show()


# Reading the dataset
df = pd.read_excel("data/ages_genders_turnout.xlsx")
df_2013 = df.loc[(df["year"]==2013) & (df["gender"]!="total")]
df_2017 = df.loc[(df["year"]==2017) & (df["gender"]!="total")]


# Defining palette for assign each category a color
color_to_cat2 = dict(male = "turquoise",
                    female = "violet")


sns.set_style("white")
# Customizing the first figure
fig, ax = plt.subplots(2, 1, figsize=(15, 9))
sns.barplot(ax=ax[0],
            y='turnout',
            x="age_group",
            hue='gender',
            data=df_2013,
            palette=color_to_cat2,
            ci=None)

plt.title("Turnout by Age and Gender ", x=0.5, y=2.3, fontsize = 27)
ax[0].set_title("Year: 2013", x=1, y=-0.15, fontsize=15)
ax[0].set_ylabel("")
ax[0].set_xlabel("")
ax[0].tick_params(labelsize=15)
ax[0].legend(title="", fontsize=15, bbox_to_anchor=(1, 1), labels=['Male', "Female"])


# Customizing the second figure
sns.barplot(ax=ax[1],
            y='turnout',
            x="age_group",
            hue='gender',
            data=df_2017,
            palette=color_to_cat2,
            ci=None)

plt.suptitle("Year: 2017", x=0.9, y=0.1, fontsize=15)
ax[1].set_ylabel("% Turnout", fontsize=20, y=1.1)
ax[1].set_xlabel("", fontsize = 16)
ax[1].tick_params(labelsize=15)
ax[1].legend([])
sns.despine()


# Setting the dataframe
df_2013 = df.loc[(df["year"]==2013) & (df["gender"]=="total")]
df_2017 = df.loc[(df["year"]==2017) & (df["gender"]=="total")]


# Setting the figure size
fig, ax = plt.subplots(figsize=(15, 9))


# First plot
sns.barplot(ax=ax,
            y="turnout", 
            x="age_group",
            data=df_2017,
            ci=None,
            color='orange')


# Second plot
sns.barplot(ax=ax,
            y="turnout", 
            x="age_group",
            data=df_2013,
            ci=None,
            color='khaki')


# Customizing the figure
top_bar = mpatches.Patch(color='orange', label='2017')
bottom_bar = mpatches.Patch(color='khaki', label='2013')
plt.legend(handles=[top_bar, bottom_bar], fontsize=14, bbox_to_anchor=(1.11, 1))
plt.title("Turnout by Age in 2013 and 2017", y=1, x=0.22, fontsize=25)
plt.xlabel("", fontsize=16)
plt.ylabel("% Turnout", fontsize=16)
plt.tick_params(labelsize=15)

fig.show()


# Setting the dataframe
df_2013 = df.loc[(df["year"]==2013) & (df["gender"]!="total")]
df_2017 = df.loc[(df["year"]==2017) & (df["gender"]!="total")]


# Setting the figure size
fig, ax = plt.subplots(2, 1, figsize=(15, 9))


# First plot 2013
sns.barplot(ax=ax[0],
            y="entitled_to_vote", 
            x="age_group",
            hue="gender",
            data=df_2013,
            palette=dict(male = "steelblue",
                    female = "mediumvioletred"),
            ci=None)


# Second plot 2013
sns.barplot(ax=ax[0],
            y="non_voters", 
            x="age_group",
            hue="gender",
            data=df_2013,
            palette=color_to_cat2,
            ci=None)


# Customizing the figure
plt.title("Number of Voters and Non-Voters by Age and Gender", y=2.3, x=0.36, fontsize=25)
ax[0].set_title("Year: 2013", x=1, y=-0.15, fontsize=15)
ax[0].set_ylabel("")
ax[0].set_xlabel("")
ax[0].tick_params(labelsize=15)
ax[0].legend(title="",
             fontsize=15,
             bbox_to_anchor=(1, 0.7),
             labels=["Male Voters", "Female Voters", "Male Non-Voters", "Female Non-Votes"])
ax[0].yaxis.set_major_formatter(ticker.EngFormatter())



# First plot 2017
sns.barplot(ax=ax[1],
            y="entitled_to_vote", 
            x="age_group",
            hue="gender",
            data=df_2017,
            palette=dict(male = "steelblue",
                    female = "mediumvioletred"),
            ci=None)


# Second plot 2017
sns.barplot(ax=ax[1],
            y="non_voters", 
            x="age_group",
            hue="gender",
            data=df_2017,
            palette=color_to_cat2,
            ci=None)


# Customizing the figure
plt.suptitle("Year: 2017", x=0.9, y=0.1, fontsize=15)
ax[1].set_ylabel("")
ax[1].set_xlabel("", fontsize = 16)
ax[1].tick_params(labelsize=15)
ax[1].legend([])
ax[1].yaxis.set_major_formatter(ticker.EngFormatter())
sns.despine()


# Reading the datasets.(Vote numbers by regions)
data = pd.read_excel("data/regions_parties.xlsx")
df_2013 = data[data["year"]==2013]


# Changing column name to merge the datasets
shp.rename(columns={'ID_1':'land_number'}, inplace=True)


# Creating a column to use when coloring the map
df_2013["winner_party"] = df_2013.iloc[:, 4:].idxmax(axis=1)


# Defining a function to convert shp to geojson format
def df_to_geojson(df, properties, geom='geometry'):
    geojson = {'type':'FeatureCollection', 'features':[]}
    k = 0
    for _, row in df.iterrows():
        features = {'type':'Feature',
                   'properties':{},
                   'geometry':{}}
        features["geometry"] = mapping(row[geom])
        for prop in properties:
            features['properties'][prop] = row[prop]
        geojson['features'].append(features)
        k += 1
    return geojson


# Converting the dataframe to gejson
cols = ["land_number"]
geojson = df_to_geojson(shp, cols)


# Merging two datasets
geo_df = gpd.GeoDataFrame.from_features(
    geojson["features"]
).merge(df_2013, on="land_number").set_index("land_number")


# Defining colormap to assign correct colors to the parties
cmap = LinearSegmentedColormap.from_list('mycmap', [(0, color_to_cat["CDU"]), (0.1, color_to_cat["CSU"]),
                                                    (0.2, color_to_cat["SPD"]), (1, color_to_cat["AfD"])])


# Plotting the figure
f, axes = plt.subplots(figsize=(20, 10), ncols=2)
geo_df[geo_df["votes"]=="first"].plot(ax=axes[0], column='winner_party', categories=["CDU","SPD","CSU","AfD"], cmap=cmap, legend=True)
geo_df[geo_df["votes"]=="second"].plot(ax=axes[1], column='winner_party', categories=["CDU","SPD","CSU","AfD"], cmap=cmap, legend=True)


# Setting the titles
axes[0].set_title("First Votes", size=14)
axes[1].set_title("Second Votes", size=14)
plt.text(0.3, 56, "2013 German Federal Election: Winner Parties", size=20)


# Rendering the plot
plt.show()


# Filtering the dataset
df_2017 = data[data["year"]==2017]


# Changing column name to merge the datasets
shp.rename(columns={'ID_1':'land_number'}, inplace=True)


# Creating a column to use when coloring the map
df_2017["winner_party"] = df_2017.iloc[:, 4:].idxmax(axis=1)


# Defining a function to convert shp to geojson format
def df_to_geojson(df, properties, geom='geometry'):
    geojson = {'type':'FeatureCollection', 'features':[]}
    k = 0
    for _, row in df.iterrows():
        features = {'type':'Feature',
                   'properties':{},
                   'geometry':{}}
        features["geometry"] = mapping(row[geom])
        for prop in properties:
            features['properties'][prop] = row[prop]
        geojson['features'].append(features)
        k += 1
    return geojson


# Converting the dataframe to gejson
cols = ["land_number"]
geojson = df_to_geojson(shp, cols)


# Merging two datasets
geo_df = gpd.GeoDataFrame.from_features(
    geojson["features"]
).merge(df_2017, on="land_number").set_index("land_number")

    
# Plotting the figure
f, axes = plt.subplots(figsize=(20, 10), ncols=2)
geo_df[geo_df["votes"]=="first"].plot(ax=axes[0], column='winner_party', categories=["CDU","SPD","CSU","AfD"], cmap=cmap, legend=True)
geo_df[geo_df["votes"]=="second"].plot(ax=axes[1], column='winner_party', categories=["CDU","SPD","CSU","AfD"], cmap=cmap, legend=True)


# Setting the titles
axes[0].set_title("First Votes", size=14)
axes[1].set_title("Second Votes", size=14)
plt.text(0.3, 56, "2017 German Federal Election: Winner Parties", size=20)


# Rendering the plot
plt.show()


# Sorting the regions by vote number for each political party for first votes in 2013
df_2013_first = df_2013[df_2013["votes"]=="first"]
df_2013_first.set_index("land", inplace=True)
df_2013_first.drop(["land_number", "year", "votes", "winner_party"], axis=1, inplace=True)
df_2013_first = df_2013_first.transpose()


# Creating a dataframe for sorted regions
df_sorted = pd.DataFrame(columns=range(1, 17))
k = 0
for row in df_2013_first.iterrows():
    df_sorted.loc[k, :] = df_2013_first.sort_values(by=row[0], axis=1, ascending=False).columns
    k += 1
df_sorted.index = df_2013_first.index
df_sorted


# Sorting the regions by vote number for each political party for second votes in 2013
df_2013_second = df_2013[df_2013["votes"]=="second"]
df_2013_second.set_index("land", inplace=True)
df_2013_second.drop(["land_number", "year", "votes", "winner_party"], axis=1, inplace=True)
df_2013_second = df_2013_second.transpose()


# Creating a dataframe for sorted regions
df_sorted = pd.DataFrame(columns=range(1, 17))
k = 0
for row in df_2013_second.iterrows():
    df_sorted.loc[k, :] = df_2013_second.sort_values(by=row[0], axis=1, ascending=False).columns
    k += 1
df_sorted.index = df_2013_second.index
df_sorted


# Sorting the regions by vote number for each political party for first votes in 2017
df_2017_first = df_2017[df_2017["votes"]=="first"]
df_2017_first.set_index("land", inplace=True)
df_2017_first.drop(["land_number", "year", "votes", "winner_party"], axis=1, inplace=True)
df_2017_first = df_2017_first.transpose()


# Creating a dataframe for sorted regions
df_sorted = pd.DataFrame(columns=range(1, 17))
k = 0
for row in df_2017_first.iterrows():
    df_sorted.loc[k, :] = df_2017_first.sort_values(by=row[0], axis=1, ascending=False).columns
    k += 1
df_sorted.index = df_2017_first.index
df_sorted


# Sorting the regions by vote number for each political party for second votes in 2017
df_2017_second = df_2017[df_2017["votes"]=="second"]
df_2017_second.set_index("land", inplace=True)
df_2017_second.drop(["land_number", "year", "votes", "winner_party"], axis=1, inplace=True)
df_2017_second = df_2017_second.transpose()


# Creating a dataframe for sorted regions
df_sorted = pd.DataFrame(columns=range(1, 17))
k = 0
for row in df_2017_second.iterrows():
    df_sorted.loc[k, :] = df_2017_second.sort_values(by=row[0], axis=1, ascending=False).columns
    k += 1
df_sorted.index = df_2017_second.index
df_sorted


# Sorting the regions by vote number for each political party for first votes in 2013
df_2013_first = df_2013[df_2013["votes"]=="first"]
df_2013_first.set_index("land", inplace=True)
df_2013_first.drop(["land_number", "year", "votes"], axis=1, inplace=True)


# Creating a dataframe to hold the least amount of votes for the second most voted party to win
df_second_parties = pd.DataFrame(columns=["winner_party", "second_party", "needed_votes"], index=df_2013_first.index)
k = 0
for row in df_2013_first.iterrows():
    winner_party = row[1][8]
    df_second_parties["winner_party"][k] = df_2013_first[winner_party][k]
    second_party = df_2013_first.iloc[:, :8].sort_values(by=row[0], axis=1, ascending=False).columns[1]
    df_second_parties["second_party"][k] = df_2013_first[second_party][k]
    df_second_parties["needed_votes"][k] = df_second_parties["winner_party"][k] - df_second_parties["second_party"][k]
    k += 1
df_second_parties


# Sorting the regions by vote number for each political party for second votes in 2013
df_2013_second = df_2013[df_2013["votes"]=="second"]
df_2013_second.set_index("land", inplace=True)
df_2013_second.drop(["land_number", "year", "votes"], axis=1, inplace=True)


# Creating a dataframe to hold the least amount of votes for the second most voted party to win
df_second_parties = pd.DataFrame(columns=["winner_party", "second_party", "needed_votes"], index=df_2013_second.index)
k = 0
for row in df_2013_second.iterrows():
    winner_party = row[1][8]
    df_second_parties["winner_party"][k] = df_2013_second[winner_party][k]
    second_party = df_2013_second.iloc[:, :8].sort_values(by=row[0], axis=1, ascending=False).columns[1]
    df_second_parties["second_party"][k] = df_2013_second[second_party][k]
    df_second_parties["needed_votes"][k] = df_second_parties["winner_party"][k] - df_second_parties["second_party"][k]
    k += 1
df_second_parties


# Sorting the regions by vote number for each political party for first votes in 2017
df_2017_first = df_2017[df_2017["votes"]=="first"]
df_2017_first.set_index("land", inplace=True)
df_2017_first.drop(["land_number", "year", "votes"], axis=1, inplace=True)


# Creating a dataframe to hold the least amount of votes for the second most voted party to win
df_second_parties = pd.DataFrame(columns=["winner_party", "second_party", "needed_votes"], index=df_2017_first.index)
k = 0
for row in df_2017_first.iterrows():
    winner_party = row[1][8]
    df_second_parties["winner_party"][k] = df_2017_first[winner_party][k]
    second_party = df_2017_first.iloc[:, :8].sort_values(by=row[0], axis=1, ascending=False).columns[1]
    df_second_parties["second_party"][k] = df_2017_first[second_party][k]
    df_second_parties["needed_votes"][k] = df_second_parties["winner_party"][k] - df_second_parties["second_party"][k]
    k += 1
df_second_parties


# Sorting the regions by vote number for each political party for second votes in 2017
df_2017_second = df_2017[df_2017["votes"]=="second"]
df_2017_second.set_index("land", inplace=True)
df_2017_second.drop(["land_number", "year", "votes"], axis=1, inplace=True)


# Creating a dataframe to hold the least amount of votes for the second most voted party to win
df_second_parties = pd.DataFrame(columns=["winner_party", "second_party", "needed_votes"], index=df_2017_second.index)
k = 0
for row in df_2017_second.iterrows():
    winner_party = row[1][8]
    df_second_parties["winner_party"][k] = df_2017_second[winner_party][k]
    second_party = df_2017_second.iloc[:, :8].sort_values(by=row[0], axis=1, ascending=False).columns[1]
    df_second_parties["second_party"][k] = df_2017_second[second_party][k]
    df_second_parties["needed_votes"][k] = df_second_parties["winner_party"][k] - df_second_parties["second_party"][k]
    k += 1
df_second_parties

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
CDU	Nordrhein-Westfalen	Baden-Württemberg	Niedersachsen	Hessen	Sachsen	Rheinland-Pfalz	Schleswig-Holstein	Berlin	Thüringen	Brandenburg	Sachsen-Anhalt	Mecklenburg-Vorpommern	Hamburg	Saarland	Bremen	Bayern
SPD	Nordrhein-Westfalen	Niedersachsen	Bayern	Baden-Württemberg	Hessen	Rheinland-Pfalz	Schleswig-Holstein	Berlin	Sachsen	Brandenburg	Hamburg	Thüringen	Sachsen-Anhalt	Saarland	Mecklenburg-Vorpommern	Bremen
FDP	Nordrhein-Westfalen	Bayern	Baden-Württemberg	Niedersachsen	Hessen	Rheinland-Pfalz	Sachsen	Schleswig-Holstein	Berlin	Brandenburg	Sachsen-Anhalt	Thüringen	Hamburg	Mecklenburg-Vorpommern	Saarland	Bremen
DIE_LINKE	Sachsen	Nordrhein-Westfalen	Berlin	Brandenburg	Sachsen-Anhalt	Thüringen	Baden-Württemberg	Bayern	Mecklenburg-Vorpommern	Niedersachsen	Hessen	Rheinland-Pfalz	Hamburg	Schleswig-Holstein	Saarland	Bremen
GRÜNE	Baden-Württemberg	Nordrhein-Westfalen	Bayern	Niedersachsen	Berlin	Hessen	Rheinland-Pfalz	Schleswig-Holstein	Sachsen	Hamburg	Brandenburg	Thüringen	Sachsen-Anhalt	Bremen	Mecklenburg-Vorpommern	Saarland
CSU	Bayern	Baden-Württemberg	Berlin	Brandenburg	Bremen	Hamburg	Hessen	Mecklenburg-Vorpommern	Niedersachsen	Nordrhein-Westfalen	Rheinland-Pfalz	Saarland	Sachsen	Sachsen-Anhalt	Schleswig-Holstein	Thüringen
AfD	Nordrhein-Westfalen	Bayern	Baden-Württemberg	Niedersachsen	Berlin	Schleswig-Holstein	Hessen	Hamburg	Saarland	Thüringen	Sachsen-Anhalt	Bremen	Rheinland-Pfalz	Brandenburg	Mecklenburg-Vorpommern	Sachsen
other	Bayern	Nordrhein-Westfalen	Baden-Württemberg	Sachsen	Rheinland-Pfalz	Hessen	Niedersachsen	Berlin	Brandenburg	Thüringen	Sachsen-Anhalt	Mecklenburg-Vorpommern	Schleswig-Holstein	Hamburg	Saarland	Bremen

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
CDU	Nordrhein-Westfalen	Baden-Württemberg	Niedersachsen	Hessen	Sachsen	Rheinland-Pfalz	Schleswig-Holstein	Berlin	Sachsen-Anhalt	Brandenburg	Thüringen	Mecklenburg-Vorpommern	Hamburg	Saarland	Bremen	Bayern
SPD	Nordrhein-Westfalen	Niedersachsen	Bayern	Baden-Württemberg	Hessen	Rheinland-Pfalz	Schleswig-Holstein	Berlin	Sachsen	Brandenburg	Hamburg	Sachsen-Anhalt	Thüringen	Saarland	Mecklenburg-Vorpommern	Bremen
FDP	Nordrhein-Westfalen	Baden-Württemberg	Bayern	Niedersachsen	Hessen	Rheinland-Pfalz	Schleswig-Holstein	Sachsen	Berlin	Hamburg	Brandenburg	Thüringen	Sachsen-Anhalt	Saarland	Mecklenburg-Vorpommern	Bremen
DIE_LINKE	Nordrhein-Westfalen	Sachsen	Berlin	Brandenburg	Thüringen	Sachsen-Anhalt	Baden-Württemberg	Bayern	Niedersachsen	Hessen	Mecklenburg-Vorpommern	Rheinland-Pfalz	Schleswig-Holstein	Hamburg	Saarland	Bremen
GRÜNE	Nordrhein-Westfalen	Baden-Württemberg	Bayern	Niedersachsen	Hessen	Berlin	Rheinland-Pfalz	Schleswig-Holstein	Sachsen	Hamburg	Brandenburg	Thüringen	Sachsen-Anhalt	Bremen	Mecklenburg-Vorpommern	Saarland
CSU	Bayern	Baden-Württemberg	Berlin	Brandenburg	Bremen	Hamburg	Hessen	Mecklenburg-Vorpommern	Niedersachsen	Nordrhein-Westfalen	Rheinland-Pfalz	Saarland	Sachsen	Sachsen-Anhalt	Schleswig-Holstein	Thüringen
AfD	Nordrhein-Westfalen	Baden-Württemberg	Bayern	Hessen	Niedersachsen	Sachsen	Rheinland-Pfalz	Berlin	Brandenburg	Thüringen	Schleswig-Holstein	Sachsen-Anhalt	Mecklenburg-Vorpommern	Hamburg	Saarland	Bremen
other	Bayern	Nordrhein-Westfalen	Baden-Württemberg	Sachsen	Niedersachsen	Hessen	Berlin	Rheinland-Pfalz	Thüringen	Brandenburg	Schleswig-Holstein	Sachsen-Anhalt	Mecklenburg-Vorpommern	Hamburg	Saarland	Bremen

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
CDU	Nordrhein-Westfalen	Baden-Württemberg	Niedersachsen	Hessen	Rheinland-Pfalz	Sachsen	Schleswig-Holstein	Berlin	Brandenburg	Thüringen	Sachsen-Anhalt	Mecklenburg-Vorpommern	Hamburg	Saarland	Bremen	Bayern
SPD	Nordrhein-Westfalen	Niedersachsen	Bayern	Baden-Württemberg	Hessen	Rheinland-Pfalz	Schleswig-Holstein	Berlin	Hamburg	Brandenburg	Sachsen	Sachsen-Anhalt	Thüringen	Saarland	Mecklenburg-Vorpommern	Bremen
FDP	Nordrhein-Westfalen	Baden-Württemberg	Bayern	Niedersachsen	Hessen	Sachsen	Rheinland-Pfalz	Schleswig-Holstein	Berlin	Sachsen-Anhalt	Brandenburg	Thüringen	Hamburg	Mecklenburg-Vorpommern	Bremen	Saarland
DIE_LINKE	Nordrhein-Westfalen	Sachsen	Bayern	Berlin	Baden-Württemberg	Niedersachsen	Brandenburg	Sachsen-Anhalt	Thüringen	Hessen	Mecklenburg-Vorpommern	Rheinland-Pfalz	Hamburg	Schleswig-Holstein	Saarland	Bremen
GRÜNE	Baden-Württemberg	Bayern	Nordrhein-Westfalen	Niedersachsen	Hessen	Berlin	Schleswig-Holstein	Rheinland-Pfalz	Hamburg	Sachsen	Brandenburg	Thüringen	Sachsen-Anhalt	Mecklenburg-Vorpommern	Bremen	Saarland
CSU	Bayern	Baden-Württemberg	Berlin	Brandenburg	Bremen	Hamburg	Hessen	Mecklenburg-Vorpommern	Niedersachsen	Nordrhein-Westfalen	Rheinland-Pfalz	Saarland	Sachsen	Sachsen-Anhalt	Schleswig-Holstein	Thüringen
AfD	Nordrhein-Westfalen	Bayern	Baden-Württemberg	Sachsen	Hessen	Niedersachsen	Thüringen	Brandenburg	Rheinland-Pfalz	Berlin	Sachsen-Anhalt	Mecklenburg-Vorpommern	Schleswig-Holstein	Hamburg	Saarland	Bremen
other	Bayern	Baden-Württemberg	Nordrhein-Westfalen	Berlin	Sachsen	Rheinland-Pfalz	Hessen	Brandenburg	Niedersachsen	Thüringen	Sachsen-Anhalt	Mecklenburg-Vorpommern	Hamburg	Schleswig-Holstein	Saarland	Bremen

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
CDU	Nordrhein-Westfalen	Baden-Württemberg	Niedersachsen	Hessen	Rheinland-Pfalz	Sachsen	Schleswig-Holstein	Berlin	Brandenburg	Sachsen-Anhalt	Thüringen	Mecklenburg-Vorpommern	Hamburg	Saarland	Bremen	Bayern
SPD	Nordrhein-Westfalen	Niedersachsen	Bayern	Baden-Württemberg	Hessen	Rheinland-Pfalz	Schleswig-Holstein	Berlin	Brandenburg	Sachsen	Hamburg	Sachsen-Anhalt	Thüringen	Saarland	Mecklenburg-Vorpommern	Bremen
FDP	Nordrhein-Westfalen	Baden-Württemberg	Bayern	Niedersachsen	Hessen	Rheinland-Pfalz	Schleswig-Holstein	Sachsen	Berlin	Hamburg	Brandenburg	Thüringen	Sachsen-Anhalt	Mecklenburg-Vorpommern	Saarland	Bremen
DIE_LINKE	Nordrhein-Westfalen	Bayern	Sachsen	Baden-Württemberg	Berlin	Niedersachsen	Hessen	Brandenburg	Sachsen-Anhalt	Thüringen	Mecklenburg-Vorpommern	Rheinland-Pfalz	Schleswig-Holstein	Hamburg	Saarland	Bremen
GRÜNE	Baden-Württemberg	Nordrhein-Westfalen	Bayern	Niedersachsen	Hessen	Berlin	Schleswig-Holstein	Rheinland-Pfalz	Hamburg	Sachsen	Brandenburg	Thüringen	Sachsen-Anhalt	Mecklenburg-Vorpommern	Bremen	Saarland
CSU	Bayern	Baden-Württemberg	Berlin	Brandenburg	Bremen	Hamburg	Hessen	Mecklenburg-Vorpommern	Niedersachsen	Nordrhein-Westfalen	Rheinland-Pfalz	Saarland	Sachsen	Sachsen-Anhalt	Schleswig-Holstein	Thüringen
AfD	Nordrhein-Westfalen	Bayern	Baden-Württemberg	Sachsen	Niedersachsen	Hessen	Brandenburg	Thüringen	Rheinland-Pfalz	Sachsen-Anhalt	Berlin	Mecklenburg-Vorpommern	Schleswig-Holstein	Hamburg	Saarland	Bremen
other	Bayern	Nordrhein-Westfalen	Baden-Württemberg	Niedersachsen	Sachsen	Hessen	Berlin	Brandenburg	Rheinland-Pfalz	Thüringen	Sachsen-Anhalt	Schleswig-Holstein	Mecklenburg-Vorpommern	Hamburg	Saarland	Bremen

	winner_party	second_party	needed_votes
land
Baden-Württemberg	2873905	1332623	1541282
Bayern	3544079	1443710	2100369
Berlin	536332	455138	81194
Brandenburg	492236	367713	124523
Bremen	133437	97265	36172
Hamburg	336337	305979	30358
Hessen	1399206	1080828	318378
Mecklenburg-Vorpommern	384607	204479	180128
Niedersachsen	1946518	1699556	246962
Nordrhein-Westfalen	4148811	3472520	676291
Rheinland-Pfalz	1026360	716029	310331
Saarland	234133	199723	34410
Sachsen	1080087	500300	579787
Sachsen-Anhalt	491418	299032	192386
Schleswig-Holstein	708702	596882	111820
Thüringen	508083	298821	209262

Table of Contents¶

Importing necessary libraries¶

Votes by Age Groups - 2013¶

Votes by Age Groups - 2017¶

Percentage Change of First Votes¶

Percentage Change of Second Votes¶

Percentage of Age Groups by Parties - 2013¶

Percentage of Age Groups by Parties - 2017¶

Turnout by Regions¶

Voters and Non-Voters by Regions - 2013¶

Voters and Non-Voters by Regions - 2017¶

Turnout by Age and Gender¶

Turnout by Age¶

Voters and Non-Voters by Age and Gender¶

Winner Parties Choropleth - 2013¶

Winner Parties Choropleth - 2017¶

Sorting the Regions¶

Least Amount of Votes for the Second Most Voted Party to Win¶

	winner_party	second_party	needed_votes
land
Baden-Württemberg	2576606	1160424	1416182
Bayern	3243569	1314009	1929560
Berlin	508643	439387	69256
Brandenburg	482601	321174	161427
Bremen	117204	96459	20745
Hamburg	288902	285927	2975
Hessen	1232994	906906	326088
Mecklenburg-Vorpommern	369048	186871	182177
Niedersachsen	1825592	1470005	355587
Nordrhein-Westfalen	3776563	3028282	748281
Rheinland-Pfalz	958655	608910	349745
Saarland	212368	174592	37776
Sachsen	994601	467045	527556
Sachsen-Anhalt	485781	282319	203462
Schleswig-Holstein	638756	513725	125031
Thüringen	477283	288615	188668

	winner_party	second_party	needed_votes
land
Baden-Württemberg	2350054	1166668	1183386
Bayern	3255487	1336404	1919083
Berlin	460296	391772	68524
Brandenburg	432157	304896	127261
Bremen	105119	81275	23844
Hamburg	311889	277677	34212
Hessen	1185393	974954	210439
Mecklenburg-Vorpommern	316662	171130	145532
Niedersachsen	1777588	1557197	220391
Nordrhein-Westfalen	3756952	3073698	683254
Rheinland-Pfalz	932382	676438	255944
Saarland	211855	184511	27344
Sachsen	756206	628048	128158
Sachsen-Anhalt	401454	238047	163407
Schleswig-Holstein	682287	493279	189008
Thüringen	408513	291466	117047

	winner_party	second_party	needed_votes
land
Baden-Württemberg	2061687	982370	1079317
Bayern	2869688	1130931	1738757
Berlin	424321	351170	73151
Brandenburg	397839	301103	96736
Bremen	88944	83409	5535
Hamburg	266312	229862	36450
Hessen	1033200	788427	244773
Mecklenburg-Vorpommern	307263	172409	134854
Niedersachsen	1623481	1275172	348309
Nordrhein-Westfalen	3214013	2557876	656137
Rheinland-Pfalz	848003	570518	277485
Saarland	189573	158895	30678
Sachsen	669940	665751	4189
Sachsen-Anhalt	377411	244401	133010
Schleswig-Holstein	583135	399505	183630
Thüringen	372258	294069	78189